package tr.edu.metu.is.mega.server.timer;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Date;

import tr.edu.metu.is.mega.server.dao.DatabaseConnectionFactory;

/**
 * Deletes expired students with all related info.
 */
public class StudentExpireController {

    public static void doJob() {
        System.out.println("StudentExpireController.doJob() "+ new Date());

        Connection con = DatabaseConnectionFactory.getConnection();
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String sql = "DELETE FROM SUBMISSION WHERE FK_SUBMITTEDBY IN  (SELECT PKID FROM SYSTEMUSER WHERE EXPIRYDATE<current_date and USERROLE='S')";
            stmt.addBatch(sql);
            sql = "DELETE FROM TEAMMEMBER WHERE FK_STUDENTID IN (SELECT PKID FROM COURSESTUDENT WHERE FK_STUDENTID IN (SELECT PKID FROM SYSTEMUSER WHERE EXPIRYDATE<current_date AND USERROLE='S'))";
            stmt.addBatch(sql);
            sql = "DELETE FROM COURSESTUDENT WHERE FK_STUDENTID IN (SELECT PKID FROM SYSTEMUSER WHERE EXPIRYDATE<current_date AND USERROLE='S')";
            stmt.addBatch(sql);
            sql = "DELETE FROM SYSTEMUSER WHERE EXPIRYDATE<current_date AND USERROLE='S'";
            stmt.addBatch(sql);

            int[] executeBatch = stmt.executeBatch();
            System.out.println(executeBatch[executeBatch.length - 1] + " students deleted");
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

    }

}
